package com.pactera.common.file;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.pactera.common.exception.ExcelTemplateException;
import com.pactera.common.utils.DateUtils;

/**
 * 
 * Excel导入导出工具类.（xlsx）
 * <pre>
 * 使用方法：
 * List&lt;ExcelHeader&gt; headers = new ArrayList&lt;ExcelHeader&gt;();
 * headers.add(new ExcelHeader("列1",120,ExcelHeader.STRING,"row1"));
 * headers.add(new ExcelHeader("列2",120,ExcelHeader.INT,"row2"));
 * headers.add(new ExcelHeader("列3",180,ExcelHeader.DOUBLE,"row3"));
 * headers.add(new ExcelHeader("列4",200,ExcelHeader.DATE_YMD,"row4"));
 * headers.add(new ExcelHeader("列5",200,ExcelHeader.DATE_YMD_HM,"row5"));
 * headers.add(new ExcelHeader("列6",240,ExcelHeader.DATE_YMD_HMS,"row6"));
 * List&lt;Map&lt;String,Object&gt;&gt; cellValues = new ArrayList&lt;Map&lt;String,Object&gt;&gt;();
 * for(int i=1;i<7;i++) {
 * 	Map&lt;String,Object&gt; map = new HashMap&lt;String,Object&gt;();
 * 	map.put("row1", "刘子涵");
 * 	map.put("row2", RandomCodeUtils.getRandomCode(i));
 * 	map.put("row3", RandomCodeUtils.getRandomCode(i));
 * 	map.put("row4", DateUtils.getNow(DateUtils.FORMAT_YMD));
 * 	map.put("row5", DateUtils.getNow(DateUtils.FORMAT_YMD_HMS));
 * 	map.put("row6", DateUtils.getNow(DateUtils.FORMAT_YMD_HMS));
 * 	cellValues.add(map);
 * }
 * ExcelUtils excelUtils = new ExcelUtils("",headers,cellValues);
 * String path = excelUtils.createExcel("/tempExcelFile/");
 * </pre>
 * @author solutionNC 2018年1月22日
 */
public class ExcelUtils {
    
    private static final String ID = "序号";// 序号
    
    public Workbook wb = null;

	/**
	 * excel数据集.
	 */
	public List<String[]> dataList = new ArrayList<String[]>(100);

    /**
     * 导出excel表格.</br>
     * web层可以直接调用.
     * @param response HttpServletResponse
     * @param excelName 表格名称
     * @param title 工作表名，StrUtils.isEmpty(title)=true默认sheet1
     * @param headers Excel表头参数对象
     * @param cellValues Excel列表数据集合
     * 
     */
    public static void exportExcel(HttpServletResponse response, String excelName, String title, 
    		List<ExcelHeader> headers, List<Map<String, Object>> cellValues) {
        int inRow = 0;//初始行数
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(title);//行
        XSSFRow row = sheet.createRow(inRow);//列
        ArrayList<ArrayList<ExcelCell>> excelCellList = makeExcelCell(headers,cellValues);
        if (CollectionUtils.isNotEmpty(excelCellList)) {
            createExcelHeader(excelCellList, wb, sheet, row);
            createExcelBody(excelCellList, wb, sheet);
        }
        //excel 文件名
        try {
			excelName = new String(excelName.getBytes("gb2312"), "ISO8859-1");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			System.err.println(excelName + " 转码失败！");
		}

        StringBuilder fileName = new StringBuilder();
        fileName.append(excelName);
        fileName.append(DateUtils.getNowTimeString("yyyyMMddHHmmss"));
        fileName.append(".xlsx");

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename="+fileName.toString());

        OutputStream ouputStream;
		try {
			ouputStream = response.getOutputStream();
			wb.write(ouputStream);
			ouputStream.flush();
			ouputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
			System.err.println(excelName + DateUtils.getNowTimeString("yyyyMMdd") + " IOException！");
		}
    }
    
    /**
	  * 创建excel临时文件到当前项目下的指定目录.</br>
	  * PS：可用于定时生成excel文件,通过返回的FiltUtils.downloadFile(URL)进行下载
	  * <pre>
	  * 1、创建失败返回null；
	  * 2、创建成功返回相对路径+';'+绝对路径。
	  * ExcelUtils.createExcel("/tempExcelFile/")=
	  * （/tempExcelFile/excel7865652582470183646.xlsx;E:\Space\core\web-utils\tempExcelFile\excel7865652582470183646.xlsx）。
	  * </pre>
	  * @param dirs 创建excel临时文件到当前项目下的指定目录
	  * @param title 工作表名，StrUtils.isEmpty(title)=true默认sheet1
	  * @param headers Excel表头参数对象
	  * @param cellValues Excel列表数据集合
	  * @return String 
	  * 
	 */
    //TODO 上面复用本方法代码生成文件
    @SuppressWarnings("static-access")
	public String createExcel(String dirs, String title, 
    		List<ExcelHeader> headers, List<Map<String, Object>> cellValues) {
    	int inRow = 0;//初始行数
    	XSSFWorkbook wb = new XSSFWorkbook();
    	XSSFSheet sheet = wb.createSheet(title);//行
    	XSSFRow row = sheet.createRow(inRow);//列
    	ArrayList<ArrayList<ExcelCell>> excelCellList = this.makeExcelCell(headers,cellValues);
    	if (CollectionUtils.isNotEmpty(excelCellList)) {
    		createExcelHeader(excelCellList, wb, sheet, row);
    		createExcelBody(excelCellList, wb, sheet);
    	}
    	File tempFile = null;
		try {
			String realPath = System.getProperty("user.dir")+dirs;
			File pathFile = new File(realPath);
			pathFile.mkdirs();
			tempFile = File.createTempFile("excel", ".xlsx", pathFile);
			OutputStream tempout = new FileOutputStream(tempFile);
			wb.write(tempout);
			return dirs+tempFile.getName()+";"+tempFile.getPath();
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}
    }

    /**
     * 创建excel体.
     * @param excelCellList 单元格数据集合
     * @param wb XSSFWorkbook
     * @param sheet XSSFSheet
     * 
     */
    private static void createExcelBody(ArrayList<ArrayList<ExcelCell>> excelCellList,
    		XSSFWorkbook wb, XSSFSheet sheet) {
        XSSFRow row;
        for (int i = 0; i < excelCellList.size(); i++) {
            ArrayList<ExcelCell> arrayList = excelCellList.get(i);
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setDataFormat(wb.createDataFormat().getFormat("0.00"));
            row = sheet.createRow(i + 1);
            int cellIndex = 0;
            if (CollectionUtils.isNotEmpty(arrayList)) {
                for (ExcelCell excelCell : arrayList) {
                    switch(excelCell.getCellType()) {
	                    case ExcelHeader.STRING :
	                    	row.createCell(cellIndex++)
	                    	.setCellValue(
	                    			StrUtils.getFirstNonNullStr(excelCell.getCellValue()));
	                    	break;
	                    case ExcelHeader.INT :
	                    	if (ID.equals(excelCell.getHeaderName())) {
	                    		row.createCell(cellIndex++).setCellValue(
	                    				Integer.parseInt(StrUtils.getFirstNonNullStr(i + 1)));
	                    	} else {
	                    		row.createCell(cellIndex++).setCellValue(
	                    				Integer.parseInt(StrUtils.getFirstNonNullStr(excelCell.getCellValue(),"0")));
	                    	}
	                    	break;
	                    case ExcelHeader.DOUBLE :
	                    	Cell totalincomeCell = row.createCell(cellIndex++,
	                    			Cell.CELL_TYPE_NUMERIC);
	                    	totalincomeCell.setCellStyle(cellStyle);
	                    	totalincomeCell.setCellValue(Double.valueOf(String
	                    			.valueOf(excelCell.getCellValue())));
	                    	break;
	                    case ExcelHeader.DATE_YMD :
	                    	if(excelCell.getCellValue() != null) {
	                    		try {
	                    			row.createCell(cellIndex++).setCellValue(String.valueOf(excelCell.getCellValue()).substring(0,10));
	                    		}catch(Exception e){
	                    			row.createCell(cellIndex++).setCellValue("");
	                    		}
	                    	}else {
	                    		row.createCell(cellIndex++).setCellValue("");
	                    	}
	                    	break;
	                    case ExcelHeader.DATE_YMD_HM :
	                    	if(excelCell.getCellValue() != null) {
	                    		try {
	                    			row.createCell(cellIndex++).setCellValue(String.valueOf(excelCell.getCellValue()).substring(0,16));
	                    		}catch(Exception e){
	                    			row.createCell(cellIndex++).setCellValue("");
	                    		}
	                    	}else {
	                    		row.createCell(cellIndex++).setCellValue("");
	                    	}
	                    	break;
	                    case ExcelHeader.DATE_YMD_HMS :
	                    	if(excelCell.getCellValue() != null) {
	                    		try {
	                    			row.createCell(cellIndex++).setCellValue(String.valueOf(excelCell.getCellValue()).substring(0,19));
	                    		}catch(Exception e){
	                    			row.createCell(cellIndex++).setCellValue("");
	                    		}
	                    	}else {
	                    		row.createCell(cellIndex++).setCellValue("");
	                    	}
	                    	break;
	                    default:
                    }
                }
            }

        }
    }

    /**
     * 创建excel首行.
     * @param excelCellList 单元格数据集合,获取第一个数据使用
     * @param wb HSSFWorkbook
     * @param sheet HSSFSheet
     * @param row HSSFRow
     * 
     */
    private static void createExcelHeader(
            ArrayList<ArrayList<ExcelCell>> excelCellList, XSSFWorkbook wb,
            XSSFSheet sheet, XSSFRow row) {
        ArrayList<ExcelCell> rowList = excelCellList.get(0);
        if (CollectionUtils.isNotEmpty(rowList)) {
            for (int i = 0; i < rowList.size(); i++) {
                ExcelCell excelCell = rowList.get(i);
                String headerName = excelCell.getHeaderName();
                int cellWidth = excelCell.getCellWidth();
                XSSFCellStyle style = wb.createCellStyle();// 单元格
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(headerName);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i);
                sheet.setColumnWidth(i, 32 * cellWidth);
            }
        }
    }

    /**
     * 后台数据整合成单元格对象类型的数据.
     * @param excelList 单元格数据集合
     * @param cellTypes 单元格类型集合
     * @param paramList 字段名称集合
     * @param excelWidths 表格宽度集合
     * @param excelHeaders 单元格首行集合
     * @return ArrayList&lt;ArrayList&lt;ExcelCell&gt;&gt;
     * 
     */
    private static ArrayList<ArrayList<ExcelCell>> makeExcelCell(List<ExcelHeader> headers, List<Map<String, Object>> cellValues) {
        ArrayList<ArrayList<ExcelCell>> excelCellList = new ArrayList<ArrayList<ExcelCell>>();
        if (CollectionUtils.isNotEmpty(cellValues)) {
            for (Map<String, Object> map : cellValues) {
                if (CollectionUtils.isNotEmpty(map)) {
                    ArrayList<ExcelCell> rowList = new ArrayList<ExcelCell>();
                    if(CollectionUtils.isNotEmpty(headers)){
                        for (ExcelHeader excelHeader : headers) {
                            ExcelCell excelCell = new ExcelCell();
                            excelCell.setCellType(excelHeader.getValueType());
                            excelCell.setCellValue(StrUtils.getFirstNonNullStr(map.get(excelHeader.getMapKey())));
                            excelCell.setCellWidth(excelHeader.getCellWidth());
                            excelCell.setHeaderName(excelHeader.getHeaderName());
                            rowList.add(excelCell);
                        } 
                    }
                    excelCellList.add(rowList);
                }
            }
        }
        return excelCellList;
    }
    
    /**
     * 获取Excel中第一个Sheet的所有数据，包含header.
     * <pre>
     * getAllData() 等价于 getAllData(0)
     * </pre>
	 * @param path 文件绝对路径.
	 * @param columnNum excel模板的列数，与文件不一致时，返回ExcelTemplateException异常.
     * @return List&lt;行单元格数组&gt;
     * @throws ExcelTemplateException 
     */
    public List<String[]> getAllData(String path, int columnNum) throws ExcelTemplateException {
    	return getAllData(path, 0, columnNum);
    }
    
	/**
	 * 获取Excel中第几个Sheet的所有数据，包含header.
	 * @param path 文件绝对路径.
	 * @param sheetIndex sheet索引，第几个sheet,Excel中的sheet索引从0开始.
	 * @param columnNum excel模板的列数，与文件不一致时，返回ExcelTemplateException异常.
	 * @return List&lt;行单元格数组&gt;
	 * @throws ExcelTemplateException 
	 */
    //TODO 返回对象集合。。。
	public List<String[]> getAllData(String path, int sheetIndex, int columnNum) throws ExcelTemplateException {
		try {
			InputStream input = new FileInputStream(path);
			wb = WorkbookFactory.create(input);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		validateTemplate(columnNum);
		
		int columnNumInit = 0;
		Sheet sheet = wb.getSheetAt(sheetIndex);
		if (sheet.getRow(0) != null) {
			columnNumInit = sheet.getRow(0).getLastCellNum()- sheet.getRow(0).getFirstCellNum();
		}
		if (columnNumInit > 0) {
		    Cell cell = null;
			for (Row row : sheet) {
				String[] singleRow = new String[columnNumInit];
				int n = 0;
				for (int i = 0; i < columnNumInit; i++) {
					cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_BLANK:
						singleRow[n] = "";
						break;
					case Cell.CELL_TYPE_BOOLEAN:
						singleRow[n] = Boolean.toString(cell.getBooleanCellValue());
						break;
					// 数值
					case Cell.CELL_TYPE_NUMERIC:
						if (DateUtil.isCellDateFormatted(cell)) {
							singleRow[n] = String.valueOf(cell.getDateCellValue());
						} else {
							cell.setCellType(Cell.CELL_TYPE_STRING);
							String temp = cell.getStringCellValue();
							// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
							if (temp.indexOf(".") > -1) {
								singleRow[n] = String.valueOf(new Double(temp)).trim();
							} else {
								singleRow[n] = temp.trim();
							}
						}
						break;
					case Cell.CELL_TYPE_STRING:
						singleRow[n] = cell.getStringCellValue().trim();
						break;
					case Cell.CELL_TYPE_ERROR:
						singleRow[n] = "";
						break;
					case Cell.CELL_TYPE_FORMULA:
						cell.setCellType(Cell.CELL_TYPE_STRING);
						singleRow[n] = cell.getStringCellValue();
						if (singleRow[n] != null) {
							singleRow[n] = singleRow[n].replaceAll("#N/A", "")
									.trim();
						}
						break;
					default:
						singleRow[n] = "";
						break;
					}
					n++;
				}
				//过滤空行
				if (isNotNullRow(singleRow)) {
					dataList.add(singleRow);
				}
			}
		}
		return dataList;
	}

	/**
	 * 返回数据的列数
	 * @param sheetIndex sheet索引，第几个sheet,Excel中的sheet索引从0开始.
	 * @return excel中指定sheet的总列数.
	 */
	private int getColumnNum(int sheetIndex) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		Row row = sheet.getRow(0);
		if (row != null && row.getLastCellNum() > 0) {
			return row.getLastCellNum();
		}
		return 0;
	}

	/**
	 * 获取Excel中指定sheet的某一行数据.
	 * @param sheetIndex sheet索引，第几个sheet,Excel中的sheet索引从0开始.
	 * @param rowIndex 计数从0开始，rowIndex为0代表header行
	 * @return String[]
	 */
	private String[] getRowData(int sheetIndex, int rowIndex) {
		String[] dataArray = null;
		if (rowIndex > this.getColumnNum(sheetIndex)) {
			return dataArray;
		} else {
			dataArray = new String[this.getColumnNum(sheetIndex)];
			if(dataList.size() <= rowIndex){
			    return dataArray;
			}
			return this.dataList.get(rowIndex);
		}
	}

	/**
	  * 校验是否整行数据都为空.
	  * @param cells 整行数据
	  * @return true 非空行； false 空行
	 */
	private boolean isNotNullRow(String[] cells){
		boolean isNotNull = true;
		int count = 0;
		for(String cell : cells){
			if(cell.trim().length()<=0){
				count++;
			}
		}
		
		if (count >= cells.length) {
			 isNotNull = false;
		}
		return isNotNull;
	}
    
    /**
     * 校验excel模板是否有效.
     * @param reader
     * @param columnNum
     * @return 
     * @throws ExcelTemplateException 
     * 
    */
   private void validateTemplate(int columnNum) throws ExcelTemplateException {
	   //判断sheet的最大列数是否与模板一致
       if (getColumnNum(0) != columnNum) {
           throw new ExcelTemplateException("Excel Template Invalid!");
       }

       //判断第一列（标题列)数是否与模板一致
       int titleRowNum = 1;
       String[] titles = getRowData(0, titleRowNum);
       if (titles == null || titles.length < 0 || titles.length != columnNum) {
    	   throw new ExcelTemplateException("Excel Template Invalid!");
       }
   }
   
   private static class ExcelCell {
	   
	    public String headerName;//首行名
	    
	    public int cellWidth;//宽度
	    
	    public int cellType;//字段类型
	    
	    public String cellValue;//单位格值
	    
	    public String getHeaderName() {
	        return headerName;
	    }
	    
	    public void setHeaderName(String headerName) {
	        this.headerName = headerName;
	    }

	    public int getCellWidth() {
	        return cellWidth;
	    }

	    public void setCellWidth(int cellWidth) {
	        this.cellWidth = cellWidth;
	    }

	    public int getCellType() {
	        return cellType;
	    }

	    public void setCellType(int cellType) {
	        this.cellType = cellType;
	    }

	    public String getCellValue() {
	        return cellValue;
	    }

	    public void setCellValue(String cellValue) {
	        this.cellValue = cellValue;
	    }
	}
}
